Operaciones y funciones

Introducción

En este tema vamos a aprender cómo transcribir las operaciones de conjuntos y las funciones de agregación a SQL, además de revisar diversas opciones de SQL para trabajar con valores nulos.

Operaciones sobre conjuntos

En el lenguaje SQL, las operaciones union, intersect y minus corresponden a las operaciones de conjuntos unión, intersección y resta. Las relaciones que participan en estas operaciones deben tener el mismo número y tipo de atributos.

Para ejemplificar los diferentes puntos de este tema, vamos a utilizar las siguientes relaciones, que modelan la información de venta y presupuestos de una empresa.

ventas
cliente	tienda	fecha_ventas	importe
Martínez	Granada	02-01-2018	100.25
López	Sevilla	04-01-2018	234.10
Martínez	Granada	04-01-2018	102.00
Gutiérrez	Sevilla	07-01-2018	221.45

presupuestos
cliente	fecha	importe
Martínez	12-12-2017	200.00
Gutiérrez	13-12-2017	145.65
García	15-12-2017	190.25

Operación unión

El objetivo de la operación union es combinar los resultados obtenidos de dos consultas que devuelven relaciones que son compatibles entre sí donde las tuplas repetidas se eliminan automáticamente. En caso de querer obtener todas las tuplas, indicaremos union all.

Saber los clientes que han solicitado un presupuesto o han realizado una venta:


xxxxxxxxxx
3
1
SELECT cliente FROM ventas
2
union
3
SELECT cliente FROM presupuestos;

Esta consulta devolverá {Martínez, López, Gutiérrez, García}.

Si queremos que nos muestre todos los resultados aunque estén duplicados la consulta será:


xxxxxxxxxx
3
1
SELECT cliente FROM ventas
2
union all
3
SELECT cliente FROM presupuestos;

Esta consulta devolverá {Martínez, López, Martínez, Gutiérrez, Martínez, Gutiérrez, García}.

Operación intersección

La operación de intersección se transcribe a SQL utilizando intersect. Devuelve las tuplas que aparecen en una tabla y en la otra. Al igual que en la operación de unión, los duplicados se eliminan automáticamente y para mantenerlos podemos usar intersect all.

Determinar los clientes que han realizado una compra y han solicitado un presupuesto:


xxxxxxxxxx
3
1
SELECT cliente FROM ventas
2
intersect
3
SELECT cliente FROM presupuestos;

Esta consulta devolverá {Martínez, Gutiérrez}.

Operación resta

La operación de resta se transcribe en SQL con la operación minus en mariadb se introdujo como except. Devuelve las tuplas de la primera consulta que no están en la segunda.

Obtener los clientes que han hecho alguna compra sin solicitar un presupuesto:


xxxxxxxxxx
3
1
SELECT cliente FROM ventas
2
minus
3
SELECT cliente FROM presupuestos;

Esta consulta devolverá {López}.

Consultas por intersecciones entre tablas

Podemos generar una consulta que obtenga datos de varias tablas, pudiendo establecer a su vez criterios sobre otras.

Intersecciones_tablas

Por ejemplo, dados los clientes de nuestro concesionario y nuestras ventas de coches a clientes en los diversos concesionarios, podemos obtener una consulta que obtenga datos del cliente y de la venta. Por ejemplo, nombre, apellidos, codcoche y color, mediante una consulta del tipo:


xxxxxxxxxx
3
1
SELECT nombre, apellidos, codcoche, color
2
FROM CLIENTE, VENTA
3
WHERE CLIENTE.cifcl = VENTA.idCliente

INNER JOIN

INNER JOIN implícito

Por ejemplo, para obtener los datos del cliente y el pedido en la misma consulta:


xxxxxxxxxx
3
1
SELECT nombreCliente, idPedido, fechaPedido
2
FROM cliente, pedido
3
WHERE cliente.idCliente = pedido.idCliente;

Como vemos, podemos empezar escribiendo tal cuál qué datos nos piden (SELECT), de dónde podemos obtenerlos (FROM) y qué criterio (WHERE). Esta es la versión más antigua de SQL, aunque se sigue empleando, conociéndose como JOIN implícito ya que no se usa por ningún lado la palabra JOIN, pero se está haciendo la intersección por la foreign key, en este caso la columna idCliente.

Si omitimos en el caso anterior el WHERE:

Por ejemplo, para obtener los datos del cliente y el pedido en la misma consulta:


xxxxxxxxxx
2
1
SELECT nombreCliente, idPedido, fechaPedido
2
FROM cliente, pedido;

Obtendremos el producto cartesiano de CLIENTE y PEDIDO, de forma que si hay 3 registros en CLIENTE y 4 en PEDIDO devolveremos 12 registros.

INNER JOIN natural (NATURAL JOIN)


xxxxxxxxxx
2
1
SELECT nombreCliente, idPedido, fechaPedido
2
FROM cliente NATURAL JOIN pedido;

Esta consulta obtiene lo mismo que el join implícito inicial, de forma que NATURAL JOIN establece la condición de igualdad entre los campos con el mismo nombre.

INNER JOIN explícito

La forma más habitual de INNER JOIN es la intersección de las tablas indicadas en con INNER JOIN por el campo indicado por ON.


xxxxxxxxxx
3
1
SELECT nombreCliente, idPedido, fechaPedido
2
FROM cliente INNER JOIN pedido
3
ON cliente.idCliente = pedido.idCliente;

Nota: aunque lo más frecuente es que la condición del JOIN sea en términos de igualdad entre las claves, también se podrían establecer condiciones empleando otros operadores relacionales (operadores para comparar dos valores, devolviendo un resultado booleano, es decir, cierto o falso):


xxxxxxxxxx
3
1
SELECT nombreCliente, idPedido, fechaPedido
2
FROM cliente INNER pedido PEDIDO
3
ON cliente.idCliente < pedido.idCliente;

LEFT JOIN

El resultado de esta operación siempre contiene todos los registros de la relación izquierda (primera tabla que indicamos), y aquellos de la tabla derecha que cumplen la condición establecida. Para el resto aparecerá en los campos correspondientes a dicha tabla un NULL.


xxxxxxxxxx
3
1
SELECT nombreCliente, idPedido, fechaPedido
2
FROM cliente LEFT JOIN pedido
3
ON cliente.idCliente = pedido.idCliente;

Esta consulta devolverá todos los clientes con sus pedidos, y un registro por cada cliente que no tenga pedidos.

RIGHT JOIN

El RIGHT JOIN es análogo al LEFT JOIN, pero devolviendo todos los registros de la relación derecha (segunda tabla que aparece), y únicamente aquellos de la tabla izquierda que cumplen la condición del JOIN. El resultado de esta operación siempre contiene todos los registros de la relación derecha (segunda tabla que indicamos), de modo que en aquellos sin equivalente en la parte izquierda tendrán en los campos correspondientes a dicha tabla un NULL.


xxxxxxxxxx
3
1
SELECT nombreCliente, idPedido, fechaPedido
2
FROM CLIENTE RIGHT JOIN PEDIDO
3
ON cliente.idCliente = pedido.idCliente;

Asumiendo que pudiéramos tener en la base de datos pedidos sin cliente asociado, esta consulta devolverá todos los pedidos con sus clientes, y en caso de que el cliente no aparezca, el nombreCliente sería NULL.

FULL OUTER JOIN


xxxxxxxxxx
3
1
SELECT empleado.nombre, empleado.apellidos, empresa.nombre
2
FROM empleado FULL OUTER JOIN empresa
3
ON empleado.empresa = empresa.cif;

Esta consulta devolvería tanto los datos de las empresas sin empleados como los de los empleados sin empresa, apareciendo rellenos todos los datos de la consulta únicamente para aquellos registros que cumplen la condición del JOIN, y apareciendo NULL en unos u otros campos para el resto de registros. Como se puede ver, esta consulta es equivalente a mostrar tanto los registros devueltos por el LEFT JOIN como por el RIGHT JOIN, eliminando los repetidos (aquellos registros que cumplan la condición del JOIN, que serían devueltos por ambas consultas).

Funciones de agregación

Estas toman una colección de valores como entrada y devuelven un solo valor. Las funciones de agregación en SQL son:

Media: avg
Mínimo: min
Máximo: max
Total: sum
Conteo: count, esta función devuelve el número de elementos encontrados en un grupo.

sum y avg se aplican sobre conjuntos de números, mientras que los otros operadores también pueden trabajar sobre conjuntos de datos no numéricos, como cadenas de caracteres.

Saber el importe medio de las ventas:


xxxxxxxxxx
2
1
SELECT avg (importe)
2
FROM ventas;

Saber el importe de las venta mas baja:


xxxxxxxxxx
2
1
SELECT min (importe)
2
FROM ventas;

Saber el importe de las venta más alta:


xxxxxxxxxx
2
1
SELECT max (importe)
2
FROM ventas;

Saber el importe total de las ventas:


xxxxxxxxxx
2
1
SELECT sum (importe)
2
FROM ventas;

Saber el número de ventas:


xxxxxxxxxx
2
1
SELECT count (*)
2
FROM ventas;

Devolverá el número de filas de la tabla, es decir, {4}.

Saber el número de ventas mayores de 200€:


xxxxxxxxxx
3
1
SELECT count (*)
2
FROM ventas
3
HAVING importe > 200;

Esta consulta devolverá {2}.

También podemos aplicar las funciones de agregación sobre un conjunto de tuplas utilizando group by.

Saber a suma de las ventas de nuestra empresa en cada una de sus tiendas:


xxxxxxxxxx
3
1
SELECT tienda, sum (importe)
2
FROM ventas
3
group by tienda;

Esta consulta devolverá:

tienda	importe
Granada	202.25
Sevilla	455.55

Además, es posible aplicar condiciones sobre los resultados de la operación de agregación, de tal forma que solo se muestra la tupla si, realizada la operación de agregación, se cumple la condición indicada. Para indicar esas restricciones utilizamos la cláusula HAVING.

HAVING se comporta como WHERE dentro de las funciones de agregación.

Mostrar las tiendas que han realizado ventas por un importe mayor a 400€:


xxxxxxxxxx
4
1
SELECT tienda, sum (importe)
2
FROM ventas
3
group by tienda
4
HAVING sum (importe) > 400;

Esta consulta devolverá:

tienda	importe
Sevilla	455.55

Las cláusulas WHERE y HAVING se pueden combinar en una misma consulta. Si ambas están presentes, primero se aplica la condición indicada en el WHERE; a continuación, se realiza la operación de agregación con las tuplas resultantes y, finalmente, se comprueba la condición indicada en HAVING.

Valores nulos

Los valores nulos indican que no existe información referente a un atributo, es decir, indican ausencia de información.

Para comprobar los valores nulos se utiliza is null / is not null.

Mostrar las tiendas cuyo importe de ventas este sin valor:


xxxxxxxxxx
3
1
SELECT tienda
2
FROM ventas
3
WHERE importe is null;

Esta consulta no devolverá nada, porque en nuestras tablas todos nuestros atributos tienen valores.

Uso de valores nulos en la creación de tabla

Crear una tabla cuyos atributos no puedan ser nulos:


xxxxxxxxxx
6
1
CREATE TABLE usuario(
2
id_usuario int not null,
3
nombre varchar(20) not null,
4
apellido varchar(20) not null,
5
direccion varchar(20),
6
deuda int);

Uso de valores nulos en insert y update

Cuando se especifica que un atributo no puede tener un valor nulo, es necesario que las inserciones y las actualizaciones de valores en las tuplas de dicha tabla contengan valores concretos en esos atributos. De no ser así, el sistema devolverá un error.

by Jose Manuel Pinillos